# 财务 --流水查询---收支流水日汇总----巨量千川  统计日期
import pandas as pd
from datetime import datetime

# Get current date in MM.DD format
current_date = datetime.now().strftime("%m.%d")

# 1. 修正映射表：统一名称，确保与你最后想要的输出一致
channel_mapping = {
    "1746376974191630": "自播间1.0",
    "1698365366844430": "短视频",  # 如果不需要统计短视频，后面会过滤掉
    "1771000233906184": "MIXED",  # 标记为混合账户
    "1791223031871499": "MIXED",  # 标记为混合账户
    "1791222945323017": "自播间5.0",
    "1809428201097216": "自播间5.0",
    "1829096715271305": "自播间8.0",
    "1771000173613070": "自播间8.0",
    "1799905220694019": "自播间1.0",
    "1832168569956362": "螺蛳土豆粉直播间",  # 修正名称以匹配你的需求
    "1836141698178122": "自播间8.0"
}

# 定义你需要输出的目标直播间列表（保持顺序）
target_channels = [
    "自播间1.0",
    "自播间3.0",
    "自播间5.0",
    "自播间8.0",
    "螺蛳土豆粉直播间"
]


def calculate_split_consumption():
    # 读取Excel文件（使用 pd.read_excel 时建议指定引擎，防止报错，通常不需要）
    # 请确保路径没有中文字符或者路径正确
    file_path = rf"C:\Users\20231112\Downloads\{current_date}\收支流水日汇总.xlsx"

    try:
        df = pd.read_excel(file_path)
    except FileNotFoundError:
        print(f"错误：找不到文件 {file_path}")
        return

    # 初始化结果字典，所有目标直播间初始值为0
    final_results = {ch: 0.0 for ch in target_channels}

    # 获取用户输入
    try:
        z1_input_spend = float(input("请输入混合账户中属于 [Z1] 的总消耗: "))
    except ValueError:
        print("输入错误，请输入数字。")
        return

    print("\n开始计算...\n")

    # 遍历每一行
    for index, row in df.iterrows():
        # 转换为字符串并去空格
        account_id = str(row.get('账户ID', '')).strip()

        # 获取关键数据，如果为空则填充0
        total_consume = pd.to_numeric(row.get('账户总消耗(元)'), errors='coerce') or 0
        non_gift_consume = pd.to_numeric(row.get('账户非赠款消耗(元)'), errors='coerce') or 0

        # 1. 如果不在映射表中，或总消耗为0，跳过
        if account_id not in channel_mapping or total_consume == 0:
            continue

        key_name = channel_mapping[account_id]

        # === 逻辑分支 ===

        # 情况 A：混合账户 (MIXED -> z1/z3)
        if key_name == "MIXED":
            # 计算比例：Z1输入值 / 该行总消耗
            # 注意：如果Excel里这一天有两行MIXED账户，这个输入逻辑可能需要调整（通常假设一天只有一个混合账户）
            ratio = z1_input_spend / total_consume

            # 防止输入值大于总消耗导致比例超过1
            if ratio > 1:
                ratio = 1
                print(f"警告：输入的Z1消耗大于账户ID {account_id} 的总消耗，已按100%计算。")

            # 拆分非赠款消耗
            z1_cash = non_gift_consume * ratio
            z3_cash = non_gift_consume - z1_cash

            # 累加到结果
            final_results["自播间1.0"] += z1_cash
            final_results["自播间3.0"] += z3_cash

            # 调试打印
        # print(f"[拆分] 账户{account_id}: 总非赠款{non_gift_consume:.2f} -> Z1: {z1_cash:.2f} | Z3: {z3_cash:.2f}")

        # 情况 B：纯粹账户 (直接归属)
        elif key_name in final_results:
            # 直接累加
            final_results[key_name] += non_gift_consume
            # print(f"[归属] 账户{account_id} -> {key_name}: {non_gift_consume:.2f}")

    # === 输出结果 ===
    print("-" * 30)
    print(f"{'直播间名称':<15} | {'账户非赠款消耗(元)':>15}")
    print("-" * 30)

    for channel in target_channels:
        value = final_results[channel]
        print(f"{channel:<20} {value:,.2f}")
    print("-" * 30)

    return pd.DataFrame(list(final_results.items()), columns=['直播间', '账户非赠款消耗'])


# 执行
df_result = calculate_split_consumption()



# 各店铺投流消耗日费比

# 天猫旗舰店
# 品销宝
# https://branding.taobao.com/?spm=a2e0z.13920195.cf7687754.ddb5ad158.6d856c51weVOGH#!/account/index
# 1,516 .24 元



# 阿里妈妈万相台
# https://one.alimama.com/index.html?spm=a21dvs.28490323.cf182d077.de22e78c2.2b022ced7A6i9F#!/report/account?spm=a21dvs.28490323.cf182d077.de22e78c2.2b022ced7A6i9F&rptType=account&isRequestedQztDefaultSet=1&startTime=2025-11-24&endTime=2025-11-24
# 7,837.53

# 淘宝联盟
# https://ad.alimama.com/portal/v2/report/promotionDataPage.htm?spm=a21an.14209965.c601d1c8f.1.63ff61dbM0KRNs
# 2,479.54

# 热浪引擎
# https://hot.taobao.com/hw/union/goods-alliance/databoard/overview
# 1,079.62


# 天猫超市
# https://web.txcs.tmall.com/?frameUrl=https%3A%2F%2Fweb.txcs.tmall.com%2Fpages%2Fchaoshi%2Fvendor_jbp_page_new#745313.745369.1452006#745313.745369.1452006
# 783.33

# https: // yingxiao.pinduoduo.com / mains / account / finance?msfrom = mms_sidenav

# 拼多多1店
# 26,661.51

# 拼多多2店
# 5,250.71

# 拼多多3店
# 29,003.39

# 拼多多4店
# 7,789.52

# 拼多多5店
# 2,343.37

# 拼多多6店
# 3,990.81


# https://jzt.jd.com/account/#/financialRecord
# 京东自营1店
# 1,711.10

# 京东自营2店
# 3,971.06